package handlers

import (
	"fmt"
	"net/http"
	"strings"

	"crud-go-projects/user-crud/db"

	"github.com/gin-gonic/gin"
	"gorm.io/gorm"
)

// DynamicTableHandler 处理动态表操作
type DynamicTableHandler struct{}

// NewDynamicTableHandler 创建动态表处理器
func NewDynamicTableHandler() *DynamicTableHandler {
	return &DynamicTableHandler{}
}

// CreateTable 创建新表
func (h *DynamicTableHandler) CreateTable(c *gin.Context) {
	// 获取表名
	tableName := c.Param("table")

	// 验证表名
	if !isValidTableName(tableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	// 解析字段定义
	var fields map[string]interface{}
	if err := c.ShouldBindJSON(&fields); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	// 构建创建表的SQL
	var columns []string

	// 添加ID和时间戳列，根据数据库类型调整语法
	if dbType == "mysql" {
		columns = []string{
			"id INT AUTO_INCREMENT PRIMARY KEY",
			"created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP",
			"updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
			"deleted_at TIMESTAMP NULL",
		}
	} else {
		// PostgreSQL
		columns = []string{
			"id SERIAL PRIMARY KEY",
			"created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP",
			"updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP",
			"deleted_at TIMESTAMP WITH TIME ZONE",
		}
	}

	for name, typeInfo := range fields {
		// 验证字段名
		if !isValidColumnName(name) {
			c.JSON(http.StatusBadRequest, gin.H{"error": fmt.Sprintf("Invalid column name: %s", name)})
			return
		}

		// 处理字段类型
		colType, ok := typeInfo.(map[string]interface{})
		if !ok {
			c.JSON(http.StatusBadRequest, gin.H{"error": fmt.Sprintf("Invalid field definition for: %s", name)})
			return
		}

		dataType, ok := colType["type"].(string)
		if !ok {
			c.JSON(http.StatusBadRequest, gin.H{"error": fmt.Sprintf("Missing type for field: %s", name)})
			return
		}

		// 验证并转换数据类型
		sqlType, err := mapToDBType(dataType, dbType)
		if err != nil {
			c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
			return
		}

		// 检查是否为必填字段
		required, _ := colType["required"].(bool)
		nullableStr := ""
		if required {
			nullableStr = " NOT NULL"
		}

		// 添加到列定义
		columns = append(columns, fmt.Sprintf("%s %s%s", name, sqlType, nullableStr))
	}

	// 执行创建表SQL
	createSQL := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (%s)", tableName, strings.Join(columns, ", "))
	if err := db.DB.Exec(createSQL).Error; err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusCreated, gin.H{
		"message": fmt.Sprintf("Table '%s' created successfully", tableName),
		"table":   tableName,
		"fields":  fields,
	})
}

// InsertRecord 向表中插入记录
func (h *DynamicTableHandler) InsertRecord(c *gin.Context) {
	tableName := c.Param("table")

	// 验证表名
	if !isValidTableName(tableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	// 检查表是否存在
	if !tableExists(tableName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Table '%s' does not exist", tableName)})
		return
	}

	// 解析数据
	var data map[string]interface{}
	if err := c.ShouldBindJSON(&data); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	// 构建插入SQL
	columns := make([]string, 0, len(data))
	placeholders := make([]string, 0, len(data))
	values := make([]interface{}, 0, len(data))

	i := 1
	for col, val := range data {
		// 验证字段名
		if !isValidColumnName(col) {
			c.JSON(http.StatusBadRequest, gin.H{"error": fmt.Sprintf("Invalid column name: %s", col)})
			return
		}

		columns = append(columns, col)

		// 根据数据库类型使用不同的占位符
		if dbType == "mysql" {
			placeholders = append(placeholders, "?")
		} else {
			placeholders = append(placeholders, fmt.Sprintf("$%d", i))
		}

		values = append(values, val)
		i++
	}

	// 添加created_at和updated_at
	if dbType == "mysql" {
		// MySQL 使用 NOW() 函数
		columns = append(columns, "created_at", "updated_at")
		placeholders = append(placeholders, "NOW()", "NOW()")
	} else {
		// PostgreSQL 使用 CURRENT_TIMESTAMP
		columns = append(columns, "created_at", "updated_at")
		placeholders = append(placeholders, "CURRENT_TIMESTAMP", "CURRENT_TIMESTAMP")
	}

	// 执行插入
	insertSQL := fmt.Sprintf(
		"INSERT INTO %s (%s) VALUES (%s) RETURNING id",
		tableName,
		strings.Join(columns, ", "),
		strings.Join(placeholders, ", "),
	)

	var id interface{}
	result := db.DB.Raw(insertSQL, values...).Scan(&id)

	if result.Error != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": result.Error.Error()})
		return
	}

	c.JSON(http.StatusCreated, gin.H{
		"message": "Record created successfully",
		"id":      id,
	})
}

// GetRecord 获取单条记录
func (h *DynamicTableHandler) GetRecord(c *gin.Context) {
	tableName := c.Param("table")
	id := c.Param("id")

	// 验证表名和ID
	if !isValidTableName(tableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	// 检查表是否存在
	if !tableExists(tableName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Table '%s' does not exist", tableName)})
		return
	}

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	// 构建查询，考虑软删除
	var query string
	var args []interface{}

	if dbType == "mysql" {
		// 首先检查表中是否有 deleted_at 列
		var hasDeletedAt bool
		checkColumnSQL := `
			SELECT COUNT(*) > 0 
			FROM information_schema.columns 
			WHERE table_schema = DATABASE() 
			AND table_name = ? 
			AND column_name = 'deleted_at'
		`
		db.DB.Raw(checkColumnSQL, tableName).Scan(&hasDeletedAt)

		if hasDeletedAt {
			query = fmt.Sprintf("SELECT * FROM %s WHERE id = ? AND deleted_at IS NULL", tableName)
		} else {
			query = fmt.Sprintf("SELECT * FROM %s WHERE id = ?", tableName)
		}
		args = []interface{}{id}
	} else {
		// PostgreSQL
		query = fmt.Sprintf("SELECT * FROM %s WHERE id = $1 AND deleted_at IS NULL", tableName)
		args = []interface{}{id}
	}

	// 执行查询
	var record map[string]interface{}
	if err := db.DB.Raw(query, args...).Scan(&record).Error; err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	if len(record) == 0 {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Record with id %s not found", id)})
		return
	}

	c.JSON(http.StatusOK, record)
}

// UpdateRecord 更新记录
func (h *DynamicTableHandler) UpdateRecord(c *gin.Context) {
	tableName := c.Param("table")
	id := c.Param("id")

	// 验证表名和ID
	if !isValidTableName(tableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	// 检查表是否存在
	if !tableExists(tableName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Table '%s' does not exist", tableName)})
		return
	}

	// 解析请求体
	var data map[string]interface{}
	if err := c.ShouldBindJSON(&data); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	// 构建更新SQL
	var sets []string
	var values []interface{}

	for key, value := range data {
		// 验证字段名
		if !isValidColumnName(key) {
			c.JSON(http.StatusBadRequest, gin.H{"error": fmt.Sprintf("Invalid column name: %s", key)})
			return
		}

		// 跳过id字段
		if key == "id" {
			continue
		}

		// 添加到更新列表
		if dbType == "mysql" {
			sets = append(sets, fmt.Sprintf("%s = ?", key))
		} else {
			sets = append(sets, fmt.Sprintf("%s = $%d", key, len(values)+1))
		}
		values = append(values, value)
	}

	// 添加updated_at
	if hasColumn(tableName, "updated_at") {
		if dbType == "mysql" {
			sets = append(sets, "updated_at = NOW()")
		} else {
			sets = append(sets, "updated_at = CURRENT_TIMESTAMP")
		}
	}

	// 检查是否有要更新的字段
	if len(sets) == 0 {
		c.JSON(http.StatusBadRequest, gin.H{"error": "No valid fields to update"})
		return
	}

	// 构建WHERE条件
	var whereClause string
	if hasColumn(tableName, "deleted_at") {
		if dbType == "mysql" {
			whereClause = "id = ? AND deleted_at IS NULL"
		} else {
			whereClause = fmt.Sprintf("id = $%d AND deleted_at IS NULL", len(values)+1)
		}
	} else {
		if dbType == "mysql" {
			whereClause = "id = ?"
		} else {
			whereClause = fmt.Sprintf("id = $%d", len(values)+1)
		}
	}
	values = append(values, id)

	// 执行更新
	updateSQL := fmt.Sprintf(
		"UPDATE %s SET %s WHERE %s",
		tableName,
		strings.Join(sets, ", "),
		whereClause,
	)

	result := db.DB.Exec(updateSQL, values...)
	if result.Error != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": result.Error.Error()})
		return
	}

	if result.RowsAffected == 0 {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Record with id %s not found or already deleted", id)})
		return
	}

	c.JSON(http.StatusOK, gin.H{"message": fmt.Sprintf("Record with id %s updated successfully", id)})
}

// DeleteRecord 删除记录
func (h *DynamicTableHandler) DeleteRecord(c *gin.Context) {
	tableName := c.Param("table")
	id := c.Param("id")

	// 验证表名和ID
	if !isValidTableName(tableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	// 检查表是否存在
	if !tableExists(tableName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Table '%s' does not exist", tableName)})
		return
	}

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	// 首先检查表中是否有 deleted_at 列
	var hasDeletedAt bool
	var checkColumnSQL string

	if dbType == "mysql" {
		checkColumnSQL = `
			SELECT COUNT(*) > 0 
			FROM information_schema.columns 
			WHERE table_schema = DATABASE() 
			AND table_name = ? 
			AND column_name = 'deleted_at'
		`
		db.DB.Raw(checkColumnSQL, tableName).Scan(&hasDeletedAt)
	} else {
		checkColumnSQL = `
			SELECT COUNT(*) > 0 
			FROM information_schema.columns 
			WHERE table_schema = 'public' 
			AND table_name = $1 
			AND column_name = 'deleted_at'
		`
		db.DB.Raw(checkColumnSQL, tableName).Scan(&hasDeletedAt)
	}

	var result *gorm.DB

	if hasDeletedAt {
		// 执行软删除
		var deleteSQL string
		if dbType == "mysql" {
			deleteSQL = fmt.Sprintf(
				"UPDATE %s SET deleted_at = NOW() WHERE id = ? AND deleted_at IS NULL",
				tableName,
			)
			result = db.DB.Exec(deleteSQL, id)
		} else {
			deleteSQL = fmt.Sprintf(
				"UPDATE %s SET deleted_at = CURRENT_TIMESTAMP WHERE id = $1 AND deleted_at IS NULL",
				tableName,
			)
			result = db.DB.Exec(deleteSQL, id)
		}
	} else {
		// 如果没有 deleted_at 列，执行硬删除
		var deleteSQL string
		if dbType == "mysql" {
			deleteSQL = fmt.Sprintf("DELETE FROM %s WHERE id = ?", tableName)
			result = db.DB.Exec(deleteSQL, id)
		} else {
			deleteSQL = fmt.Sprintf("DELETE FROM %s WHERE id = $1", tableName)
			result = db.DB.Exec(deleteSQL, id)
		}
	}

	if result.Error != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": result.Error.Error()})
		return
	}

	if result.RowsAffected == 0 {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Record with id %s not found or already deleted", id)})
		return
	}

	c.JSON(http.StatusOK, gin.H{"message": fmt.Sprintf("Record with id %s deleted successfully", id)})
}

// ListRecords 列出表中的所有记录
func (h *DynamicTableHandler) ListRecords(c *gin.Context) {
	tableName := c.Param("table")

	// 验证表名
	if !isValidTableName(tableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	// 检查表是否存在
	if !tableExists(tableName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Table '%s' does not exist", tableName)})
		return
	}

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	// 构建查询，考虑软删除
	var query string
	if dbType == "mysql" {
		// 首先检查表中是否有 deleted_at 列
		var hasDeletedAt bool
		checkColumnSQL := `
			SELECT COUNT(*) > 0 
			FROM information_schema.columns 
			WHERE table_schema = DATABASE() 
			AND table_name = ? 
			AND column_name = 'deleted_at'
		`
		db.DB.Raw(checkColumnSQL, tableName).Scan(&hasDeletedAt)

		if hasDeletedAt {
			query = fmt.Sprintf("SELECT * FROM %s WHERE deleted_at IS NULL", tableName)
		} else {
			query = fmt.Sprintf("SELECT * FROM %s", tableName)
		}
	} else {
		// PostgreSQL
		query = fmt.Sprintf("SELECT * FROM %s WHERE deleted_at IS NULL", tableName)
	}

	// 执行查询
	var records []map[string]interface{}
	if err := db.DB.Raw(query).Scan(&records).Error; err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusOK, gin.H{"records": records})
}

// ListTables 列出所有表
func (h *DynamicTableHandler) ListTables(c *gin.Context) {
	var tables []string

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	var query string
	if dbType == "mysql" {
		query = `
			SELECT table_name 
			FROM information_schema.tables 
			WHERE table_schema = DATABASE()
			ORDER BY table_name
		`
	} else {
		// PostgreSQL
		query = `
			SELECT table_name 
			FROM information_schema.tables 
			WHERE table_schema = 'public' 
			AND table_type = 'BASE TABLE'
			ORDER BY table_name
		`
	}

	if err := db.DB.Raw(query).Scan(&tables).Error; err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusOK, gin.H{"tables": tables})
}

// DropTable 删除表
func (h *DynamicTableHandler) DropTable(c *gin.Context) {
	tableName := c.Param("table")

	// 验证表名
	if !isValidTableName(tableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	// 检查表是否存在
	if !tableExists(tableName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Table '%s' does not exist", tableName)})
		return
	}

	// 执行删除表
	dropSQL := fmt.Sprintf("DROP TABLE %s", tableName)
	if err := db.DB.Exec(dropSQL).Error; err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusOK, gin.H{"message": fmt.Sprintf("Table '%s' dropped successfully", tableName)})
}

// GetTableStructure 获取表结构
func (h *DynamicTableHandler) GetTableStructure(c *gin.Context) {
	tableName := c.Param("table")

	// 验证表名
	if !isValidTableName(tableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	// 检查表是否存在
	if !tableExists(tableName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Table '%s' does not exist", tableName)})
		return
	}

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	// 构建查询
	var query string
	var args []interface{}

	if dbType == "mysql" {
		query = `
			SELECT 
				COLUMN_NAME as column_name, 
				DATA_TYPE as data_type,
				IS_NULLABLE as is_nullable,
				COLUMN_DEFAULT as column_default,
				COLUMN_KEY as column_key,
				EXTRA as extra
			FROM 
				INFORMATION_SCHEMA.COLUMNS 
			WHERE 
				TABLE_SCHEMA = DATABASE() 
				AND TABLE_NAME = ?
			ORDER BY 
				ORDINAL_POSITION
		`
		args = []interface{}{tableName}
	} else {
		// PostgreSQL
		query = `
			SELECT 
				column_name,
				data_type,
				is_nullable,
				column_default,
				CASE 
					WHEN pg_get_constraintdef(con.oid) LIKE '%PRIMARY KEY%' THEN 'PRI' 
					ELSE '' 
				END as column_key
			FROM 
				information_schema.columns
			LEFT JOIN 
				pg_constraint con ON con.conrelid = (
					SELECT oid FROM pg_class WHERE relname = $1
				) 
				AND con.contype = 'p' 
				AND array_position(con.conkey, columns.ordinal_position) > 0
			WHERE 
				table_schema = 'public' 
				AND table_name = $1
			ORDER BY 
				ordinal_position
		`
		args = []interface{}{tableName}
	}

	// 执行查询
	var columns []map[string]interface{}
	if err := db.DB.Raw(query, args...).Scan(&columns).Error; err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusOK, gin.H{
		"table":   tableName,
		"columns": columns,
	})
}

// RenameTable 重命名表
func (h *DynamicTableHandler) RenameTable(c *gin.Context) {
	oldTableName := c.Param("table")

	// 解析请求体获取新表名
	var req struct {
		NewName string `json:"new_name" binding:"required"`
	}

	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	newTableName := req.NewName

	// 验证表名
	if !isValidTableName(oldTableName) || !isValidTableName(newTableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	// 检查旧表是否存在
	if !tableExists(oldTableName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Table '%s' does not exist", oldTableName)})
		return
	}

	// 检查新表名是否已存在
	if tableExists(newTableName) {
		c.JSON(http.StatusConflict, gin.H{"error": fmt.Sprintf("Table '%s' already exists", newTableName)})
		return
	}

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	// 构建重命名SQL
	var renameSQL string
	if dbType == "mysql" {
		renameSQL = fmt.Sprintf("RENAME TABLE %s TO %s", oldTableName, newTableName)
	} else {
		// PostgreSQL
		renameSQL = fmt.Sprintf("ALTER TABLE %s RENAME TO %s", oldTableName, newTableName)
	}

	// 执行重命名
	if err := db.DB.Exec(renameSQL).Error; err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusOK, gin.H{
		"message":  fmt.Sprintf("Table renamed from '%s' to '%s'", oldTableName, newTableName),
		"old_name": oldTableName,
		"new_name": newTableName,
	})
}

// AddColumn 添加列
func (h *DynamicTableHandler) AddColumn(c *gin.Context) {
	tableName := c.Param("table")

	// 验证表名
	if !isValidTableName(tableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	// 检查表是否存在
	if !tableExists(tableName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Table '%s' does not exist", tableName)})
		return
	}

	// 解析请求体
	var req struct {
		Name     string      `json:"name" binding:"required"`
		Type     string      `json:"type" binding:"required"`
		Required bool        `json:"required"`
		Default  interface{} `json:"default"`
	}

	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	// 验证列名
	if !isValidColumnName(req.Name) {
		c.JSON(http.StatusBadRequest, gin.H{"error": fmt.Sprintf("Invalid column name: %s", req.Name)})
		return
	}

	// 检查列是否已存在
	if hasColumn(tableName, req.Name) {
		c.JSON(http.StatusConflict, gin.H{"error": fmt.Sprintf("Column '%s' already exists in table '%s'", req.Name, tableName)})
		return
	}

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	// 验证并转换数据类型
	sqlType, err := mapToDBType(req.Type, dbType)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	// 构建添加列SQL
	var alterSQL string

	// 处理NOT NULL和DEFAULT
	nullableStr := ""
	defaultStr := ""

	if req.Required {
		nullableStr = " NOT NULL"
	}

	if req.Default != nil {
		// 根据类型格式化默认值
		switch v := req.Default.(type) {
		case string:
			defaultStr = fmt.Sprintf(" DEFAULT '%s'", v)
		default:
			defaultStr = fmt.Sprintf(" DEFAULT %v", v)
		}
	}

	alterSQL = fmt.Sprintf("ALTER TABLE %s ADD COLUMN %s %s%s%s",
		tableName, req.Name, sqlType, nullableStr, defaultStr)

	// 执行添加列
	if err := db.DB.Exec(alterSQL).Error; err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusOK, gin.H{
		"message":  fmt.Sprintf("Column '%s' added to table '%s'", req.Name, tableName),
		"table":    tableName,
		"column":   req.Name,
		"type":     sqlType,
		"required": req.Required,
		"default":  req.Default,
	})
}

// DropColumn 删除列
func (h *DynamicTableHandler) DropColumn(c *gin.Context) {
	tableName := c.Param("table")
	columnName := c.Param("column")

	// 验证表名和列名
	if !isValidTableName(tableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	if !isValidColumnName(columnName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": fmt.Sprintf("Invalid column name: %s", columnName)})
		return
	}

	// 检查表是否存在
	if !tableExists(tableName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Table '%s' does not exist", tableName)})
		return
	}

	// 检查列是否存在
	if !hasColumn(tableName, columnName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Column '%s' does not exist in table '%s'", columnName, tableName)})
		return
	}

	// 不允许删除id、created_at、updated_at和deleted_at列
	if columnName == "id" || columnName == "created_at" || columnName == "updated_at" || columnName == "deleted_at" {
		c.JSON(http.StatusForbidden, gin.H{"error": fmt.Sprintf("Cannot drop system column '%s'", columnName)})
		return
	}

	// 构建删除列SQL
	dropSQL := fmt.Sprintf("ALTER TABLE %s DROP COLUMN %s", tableName, columnName)

	// 执行删除列
	if err := db.DB.Exec(dropSQL).Error; err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusOK, gin.H{
		"message": fmt.Sprintf("Column '%s' dropped from table '%s'", columnName, tableName),
		"table":   tableName,
		"column":  columnName,
	})
}

// GetColumn 获取列信息
func (h *DynamicTableHandler) GetColumn(c *gin.Context) {
	tableName := c.Param("table")
	columnName := c.Param("column")

	// 验证表名和列名
	if !isValidTableName(tableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	if !isValidColumnName(columnName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": fmt.Sprintf("Invalid column name: %s", columnName)})
		return
	}

	// 检查表是否存在
	if !tableExists(tableName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Table '%s' does not exist", tableName)})
		return
	}

	// 检查列是否存在
	if !hasColumn(tableName, columnName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Column '%s' does not exist in table '%s'", columnName, tableName)})
		return
	}

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	// 构建查询
	var query string
	var args []interface{}

	if dbType == "mysql" {
		query = `
			SELECT 
				COLUMN_NAME as column_name, 
				DATA_TYPE as data_type,
				CHARACTER_MAXIMUM_LENGTH as max_length,
				NUMERIC_PRECISION as numeric_precision,
				NUMERIC_SCALE as numeric_scale,
				IS_NULLABLE as is_nullable,
				COLUMN_DEFAULT as column_default,
				COLUMN_KEY as column_key,
				EXTRA as extra,
				COLUMN_COMMENT as comment
			FROM 
				INFORMATION_SCHEMA.COLUMNS 
			WHERE 
				TABLE_SCHEMA = DATABASE() 
				AND TABLE_NAME = ? 
				AND COLUMN_NAME = ?
		`
		args = []interface{}{tableName, columnName}
	} else {
		// PostgreSQL
		query = `
			SELECT 
				a.attname as column_name,
				pg_catalog.format_type(a.atttypid, a.atttypmod) as data_type,
				CASE 
					WHEN a.atttypmod > 0 AND t.typname IN ('varchar', 'char') THEN a.atttypmod - 4 
					ELSE NULL 
				END as max_length,
				CASE 
					WHEN t.typname IN ('numeric', 'decimal') THEN information_schema._pg_numeric_precision(a.atttypid, a.atttypmod)
					ELSE NULL
				END as numeric_precision,
				CASE 
					WHEN t.typname IN ('numeric', 'decimal') THEN information_schema._pg_numeric_scale(a.atttypid, a.atttypmod)
					ELSE NULL
				END as numeric_scale,
				CASE 
					WHEN a.attnotnull THEN 'NO' 
					ELSE 'YES' 
				END as is_nullable,
				pg_catalog.pg_get_expr(d.adbin, d.adrelid) as column_default,
				CASE 
					WHEN co.contype = 'p' THEN 'PRI' 
					ELSE '' 
				END as column_key,
				col_description(a.attrelid, a.attnum) as comment
			FROM 
				pg_catalog.pg_attribute a
			JOIN
				pg_catalog.pg_type t ON a.atttypid = t.oid
			LEFT JOIN 
				pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum)
			LEFT JOIN 
				pg_catalog.pg_constraint co ON (co.conrelid = a.attrelid AND a.attnum = ANY(co.conkey) AND co.contype = 'p')
			WHERE 
				a.attrelid = $1::regclass
				AND a.attname = $2
				AND a.attnum > 0 
				AND NOT a.attisdropped
		`
		args = []interface{}{tableName, columnName}
	}

	// 执行查询
	var columnInfo map[string]interface{}
	if err := db.DB.Raw(query, args...).Scan(&columnInfo).Error; err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	if len(columnInfo) == 0 {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Column '%s' not found in table '%s'", columnName, tableName)})
		return
	}

	// 处理布尔值和空值
	processColumnInfo(columnInfo)

	c.JSON(http.StatusOK, gin.H{
		"table":  tableName,
		"column": columnInfo,
	})
}

// processColumnInfo 处理列信息中的特殊值
func processColumnInfo(info map[string]interface{}) {
	// 处理 is_nullable
	if isNullable, ok := info["is_nullable"].(string); ok {
		info["is_nullable"] = strings.ToUpper(isNullable) == "YES"
	}

	// 处理 max_length
	if maxLength, ok := info["max_length"].(float64); ok && maxLength == 0 {
		info["max_length"] = nil
	}

	// 处理 numeric_precision
	if numPrecision, ok := info["numeric_precision"].(float64); ok && numPrecision == 0 {
		info["numeric_precision"] = nil
	}

	// 处理 numeric_scale
	if numScale, ok := info["numeric_scale"].(float64); ok && numScale == 0 {
		info["numeric_scale"] = nil
	}

	// 将 MySQL 的 PRI 转换为布尔值
	if columnKey, ok := info["column_key"].(string); ok {
		info["is_primary_key"] = columnKey == "PRI"
		delete(info, "column_key")
	}

	// 处理 auto_increment
	if extra, ok := info["extra"].(string); ok {
		info["is_auto_increment"] = strings.Contains(strings.ToLower(extra), "auto_increment")
		delete(info, "extra")
	}
}

// ModifyColumn 修改列属性
func (h *DynamicTableHandler) ModifyColumn(c *gin.Context) {
	tableName := c.Param("table")
	columnName := c.Param("column")

	// 验证表名和列名
	if !isValidTableName(tableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	if !isValidColumnName(columnName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": fmt.Sprintf("Invalid column name: %s", columnName)})
		return
	}

	// 检查表是否存在
	if !tableExists(tableName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Table '%s' does not exist", tableName)})
		return
	}

	// 检查列是否存在
	if !hasColumn(tableName, columnName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Column '%s' does not exist in table '%s'", columnName, tableName)})
		return
	}

	// 解析请求体
	var req struct {
		NewName   string      `json:"new_name"`
		Type      string      `json:"type"`
		Required  *bool       `json:"required"`
		Default   interface{} `json:"default"`
		Comment   string      `json:"comment"`
		MaxLength int         `json:"max_length"`
	}

	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	// 不允许修改系统列
	if columnName == "id" || columnName == "created_at" || columnName == "updated_at" || columnName == "deleted_at" {
		c.JSON(http.StatusForbidden, gin.H{"error": fmt.Sprintf("Cannot modify system column '%s'", columnName)})
		return
	}

	// 验证新列名（如果提供）
	if req.NewName != "" && req.NewName != columnName {
		if !isValidColumnName(req.NewName) {
			c.JSON(http.StatusBadRequest, gin.H{"error": fmt.Sprintf("Invalid new column name: %s", req.NewName)})
			return
		}

		// 检查新列名是否已存在
		if hasColumn(tableName, req.NewName) {
			c.JSON(http.StatusConflict, gin.H{"error": fmt.Sprintf("Column '%s' already exists in table '%s'", req.NewName, tableName)})
			return
		}
	}

	// 构建修改列SQL
	var alterSQL string
	var changes []string

	// 处理列类型修改
	if req.Type != "" {
		sqlType, err := mapToDBType(req.Type, dbType)
		if err != nil {
			c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
			return
		}

		// 处理最大长度（仅适用于字符串类型）
		if req.MaxLength > 0 && (strings.Contains(strings.ToLower(req.Type), "char") ||
			strings.Contains(strings.ToLower(req.Type), "string")) {
			if dbType == "mysql" {
				sqlType = fmt.Sprintf("VARCHAR(%d)", req.MaxLength)
			} else {
				sqlType = fmt.Sprintf("VARCHAR(%d)", req.MaxLength)
			}
		}

		if dbType == "mysql" {
			changes = append(changes, fmt.Sprintf("MODIFY COLUMN %s %s", columnName, sqlType))
		} else {
			// PostgreSQL
			changes = append(changes, fmt.Sprintf("ALTER COLUMN %s TYPE %s", columnName, sqlType))
		}
	}

	// 处理是否必填
	if req.Required != nil {
		if *req.Required {
			if dbType == "mysql" {
				changes = append(changes, fmt.Sprintf("MODIFY COLUMN %s SET NOT NULL", columnName))
			} else {
				changes = append(changes, fmt.Sprintf("ALTER COLUMN %s SET NOT NULL", columnName))
			}
		} else {
			if dbType == "mysql" {
				changes = append(changes, fmt.Sprintf("MODIFY COLUMN %s DROP NOT NULL", columnName))
			} else {
				changes = append(changes, fmt.Sprintf("ALTER COLUMN %s DROP NOT NULL", columnName))
			}
		}
	}

	// 处理默认值
	if req.Default != nil {
		var defaultStr string

		// 根据类型格式化默认值
		switch v := req.Default.(type) {
		case string:
			defaultStr = fmt.Sprintf("'%s'", v)
		default:
			defaultStr = fmt.Sprintf("%v", v)
		}

		if dbType == "mysql" {
			changes = append(changes, fmt.Sprintf("MODIFY COLUMN %s SET DEFAULT %s", columnName, defaultStr))
		} else {
			changes = append(changes, fmt.Sprintf("ALTER COLUMN %s SET DEFAULT %s", columnName, defaultStr))
		}
	}

	// 处理注释（MySQL特有）
	if req.Comment != "" && dbType == "mysql" {
		changes = append(changes, fmt.Sprintf("MODIFY COLUMN %s COMMENT '%s'", columnName, req.Comment))
	}

	// 处理列重命名
	if req.NewName != "" && req.NewName != columnName {
		if dbType == "mysql" {
			changes = append(changes, fmt.Sprintf("CHANGE COLUMN %s %s", columnName, req.NewName))
		} else {
			changes = append(changes, fmt.Sprintf("RENAME COLUMN %s TO %s", columnName, req.NewName))
		}
	}

	// 检查是否有要修改的内容
	if len(changes) == 0 {
		c.JSON(http.StatusBadRequest, gin.H{"error": "No changes specified"})
		return
	}

	// 执行修改
	for _, change := range changes {
		alterSQL = fmt.Sprintf("ALTER TABLE %s %s", tableName, change)

		if err := db.DB.Exec(alterSQL).Error; err != nil {
			c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
			return
		}
	}

	// 处理PostgreSQL的注释（需要单独处理）
	if req.Comment != "" && dbType == "postgres" {
		colName := columnName
		if req.NewName != "" {
			colName = req.NewName
		}

		commentSQL := fmt.Sprintf("COMMENT ON COLUMN %s.%s IS '%s'",
			tableName,
			colName,
			req.Comment)

		if err := db.DB.Exec(commentSQL).Error; err != nil {
			c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
			return
		}
	}

	// 修复这里的三元运算符
	newName := columnName
	if req.NewName != "" {
		newName = req.NewName
	}

	c.JSON(http.StatusOK, gin.H{
		"message":  fmt.Sprintf("Column '%s' modified successfully", columnName),
		"table":    tableName,
		"old_name": columnName,
		"new_name": newName,
		"changes":  changes,
	})
}

// ListColumns 列出表中的所有列
func (h *DynamicTableHandler) ListColumns(c *gin.Context) {
	tableName := c.Param("table")

	// 验证表名
	if !isValidTableName(tableName) {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid table name"})
		return
	}

	// 检查表是否存在
	if !tableExists(tableName) {
		c.JSON(http.StatusNotFound, gin.H{"error": fmt.Sprintf("Table '%s' does not exist", tableName)})
		return
	}

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	// 构建查询
	var query string
	var args []interface{}

	if dbType == "mysql" {
		query = `
			SELECT 
				COLUMN_NAME as column_name, 
				DATA_TYPE as data_type,
				CHARACTER_MAXIMUM_LENGTH as max_length,
				NUMERIC_PRECISION as numeric_precision,
				NUMERIC_SCALE as numeric_scale,
				IS_NULLABLE as is_nullable,
				COLUMN_DEFAULT as column_default,
				COLUMN_KEY as column_key,
				EXTRA as extra,
				COLUMN_COMMENT as comment
			FROM 
				INFORMATION_SCHEMA.COLUMNS 
			WHERE 
				TABLE_SCHEMA = DATABASE() 
				AND TABLE_NAME = ?
			ORDER BY 
				ORDINAL_POSITION
		`
		args = []interface{}{tableName}
	} else {
		// PostgreSQL
		query = `
			SELECT 
				a.attname as column_name,
				pg_catalog.format_type(a.atttypid, a.atttypmod) as data_type,
				CASE 
					WHEN a.atttypmod > 0 AND t.typname IN ('varchar', 'char') THEN a.atttypmod - 4 
					ELSE NULL 
				END as max_length,
				CASE 
					WHEN t.typname IN ('numeric', 'decimal') THEN information_schema._pg_numeric_precision(a.atttypid, a.atttypmod)
					ELSE NULL
				END as numeric_precision,
				CASE 
					WHEN t.typname IN ('numeric', 'decimal') THEN information_schema._pg_numeric_scale(a.atttypid, a.atttypmod)
					ELSE NULL
				END as numeric_scale,
				CASE 
					WHEN a.attnotnull THEN 'NO' 
					ELSE 'YES' 
				END as is_nullable,
				pg_catalog.pg_get_expr(d.adbin, d.adrelid) as column_default,
				CASE 
					WHEN co.contype = 'p' THEN 'PRI' 
					ELSE '' 
				END as column_key,
				col_description(a.attrelid, a.attnum) as comment
			FROM 
				pg_catalog.pg_attribute a
			JOIN
				pg_catalog.pg_type t ON a.atttypid = t.oid
			LEFT JOIN 
				pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum)
			LEFT JOIN 
				pg_catalog.pg_constraint co ON (co.conrelid = a.attrelid AND a.attnum = ANY(co.conkey) AND co.contype = 'p')
			WHERE 
				a.attrelid = $1::regclass
				AND a.attnum > 0 
				AND NOT a.attisdropped
			ORDER BY 
				a.attnum
		`
		args = []interface{}{tableName}
	}

	// 执行查询
	var columns []map[string]interface{}
	if err := db.DB.Raw(query, args...).Scan(&columns).Error; err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	// 处理每一列的信息
	for i := range columns {
		processColumnInfo(columns[i])
	}

	c.JSON(http.StatusOK, gin.H{
		"table":   tableName,
		"columns": columns,
	})
}

// 辅助函数

// isValidTableName 验证表名是否有效
func isValidTableName(name string) bool {
	// 只允许字母、数字和下划线，且不能以数字开头
	if len(name) == 0 || (name[0] >= '0' && name[0] <= '9') {
		return false
	}

	for _, c := range name {
		if !((c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') || (c >= '0' && c <= '9') || c == '_') {
			return false
		}
	}

	// 检查是否为保留字
	reserved := map[string]bool{
		"user": true, "group": true, "order": true, "select": true, "where": true,
		"from": true, "table": true, "index": true, "primary": true, "key": true,
	}

	return !reserved[strings.ToLower(name)]
}

// isValidColumnName 验证列名是否有效
func isValidColumnName(name string) bool {
	// 与表名验证类似
	return isValidTableName(name)
}

// mapToDBType 将API类型映射到数据库类型
func mapToDBType(apiType string, dbType string) (string, error) {
	// 根据数据库类型选择不同的类型映射
	if dbType == "mysql" {
		return mapToMySQLType(apiType)
	}
	// 默认使用 PostgreSQL 类型
	return mapToPgType(apiType)
}

// mapToPgType 将API类型映射到PostgreSQL类型
func mapToPgType(apiType string) (string, error) {
	typeMap := map[string]string{
		"string":    "TEXT",
		"text":      "TEXT",
		"int":       "INTEGER",
		"integer":   "INTEGER",
		"float":     "REAL",
		"double":    "DOUBLE PRECISION",
		"decimal":   "DECIMAL",
		"bool":      "BOOLEAN",
		"boolean":   "BOOLEAN",
		"date":      "DATE",
		"time":      "TIME",
		"datetime":  "TIMESTAMP",
		"timestamp": "TIMESTAMP WITH TIME ZONE",
		"json":      "JSONB",
	}

	pgType, ok := typeMap[strings.ToLower(apiType)]
	if !ok {
		return "", fmt.Errorf("Unsupported data type: %s", apiType)
	}

	return pgType, nil
}

// mapToMySQLType 将API类型映射到MySQL类型
func mapToMySQLType(apiType string) (string, error) {
	typeMap := map[string]string{
		"string":    "VARCHAR(255)",
		"text":      "TEXT",
		"int":       "INT",
		"integer":   "INT",
		"float":     "FLOAT",
		"double":    "DOUBLE",
		"decimal":   "DECIMAL(10,2)",
		"bool":      "BOOLEAN",
		"boolean":   "BOOLEAN",
		"date":      "DATE",
		"time":      "TIME",
		"datetime":  "DATETIME",
		"timestamp": "TIMESTAMP",
		"json":      "JSON",
	}

	sqlType, ok := typeMap[strings.ToLower(apiType)]
	if !ok {
		return "", fmt.Errorf("Unsupported data type: %s", apiType)
	}

	return sqlType, nil
}

// tableExists 检查表是否存在
func tableExists(tableName string) bool {
	var exists bool

	// 获取数据库类型
	dbType := db.GetDatabaseType()

	var query string
	var args []interface{}

	if dbType == "mysql" {
		query = `
			SELECT EXISTS (
				SELECT 1 FROM information_schema.tables 
				WHERE table_schema = DATABASE() 
				AND table_name = ?
			)
		`
		args = []interface{}{tableName}
	} else {
		// PostgreSQL
		query = `
			SELECT EXISTS (
				SELECT FROM information_schema.tables 
				WHERE table_schema = 'public' 
				AND table_name = $1
			)
		`
		args = []interface{}{tableName}
	}

	db.DB.Raw(query, args...).Scan(&exists)
	return exists
}

// hasColumn 检查表中是否存在特定列
func hasColumn(tableName, columnName string) bool {
	var exists bool
	dbType := db.GetDatabaseType()

	var query string
	var args []interface{}

	if dbType == "mysql" {
		query = `
			SELECT COUNT(*) > 0 
			FROM information_schema.columns 
			WHERE table_schema = DATABASE() 
			AND table_name = ? 
			AND column_name = ?
		`
		args = []interface{}{tableName, columnName}
	} else {
		// PostgreSQL
		query = `
			SELECT COUNT(*) > 0 
			FROM information_schema.columns 
			WHERE table_schema = 'public' 
			AND table_name = $1 
			AND column_name = $2
		`
		args = []interface{}{tableName, columnName}
	}

	db.DB.Raw(query, args...).Scan(&exists)
	return exists
}
